using System;
using System.Collections.Generic;
using System.Text;
using Mono.Data.SqliteClient;
using System.Data;

namespace Anarchy.DB
{
    class DBWrapper
    {

        #region Variables

        // Variables
        IDbConnection DB;
        IDbCommand Cmd;
        
        // Constants
        const string Table = "images";

        #endregion

        #region Constructor/Destructor
        
        /// <summary>
        /// Create a new SQLite Database handler.
        /// </summary>
        /// <param name="DatabaseFile">Filename of the SQLite database, including extension.</param>
        public DBWrapper(string DatabaseFile)
        {

            // Setup database
            OpenDatabase(DatabaseFile);

        }

        /// <summary>
        /// Destroys our SQLite Database handler.
        /// </summary>
        ~DBWrapper()
        {

            // Reset vars
            DB.Close();
            DB = null;
            Cmd = null;

        }

        #endregion

        #region Select Functions

        /// <summary>
        /// Selects the details of images from the databsae based on specified criteria.
        /// </summary>
        /// <param name="Crit">An array of Criteria which is a user-friendly way of querying the database.</param>
        /// <returns>An ImageList of images that match the specified criteria</returns>
        public ImageList Select(ImageCriteria[] Crit)
        {
            // Just call the private function that handles query.
            return Select(Crit, "");
        }

        /// <summary>
        /// Selects the details of images from the databsae ordered by a specified detail, based on specified criteria.
        /// </summary>
        /// <param name="Crit">An array of Criteria which is a user-friendly way of querying the database.</param>
        /// <param name="OrderBy">The detail of the image to order the returned values by.</param>
        /// <returns>An ImageList of images that match the specified criteria, ordered by the detail specified.</returns>
        public ImageList Select(ImageCriteria[] Crit, ImageDetail OrderBy)
        {
            // Call private function, with real field name..
            return Select(Crit, " ORDER BY " + ImageDetailToField(OrderBy));
        }

        /// <summary>
        /// Selects details of images from the database, based on specified criteria. The number of images returned is limited by the value passed.
        /// </summary>
        /// <param name="Crit">An array of Criteria which is a user-friendly way of querying the database.</param>
        /// <param name="LimitTo">The number of images to limit the return value to.</param>
        /// <returns>An ImageList of images that match the specified criteria. It is limited to the number of images specified, although the number of images may be less.</returns>
        public ImageList Select(ImageCriteria[] Crit, int LimitTo)
        {
            // Call private function, with limit added to query
            return Select(Crit, " LIMIT TO " + LimitTo.ToString());
        }
        
        /// <summary>
        /// Selects the details of images from the databsae ordered by a specified detail, based on specified criteria. The number of images returned is limited by the value passed.
        /// </summary>
        /// <param name="Crit">An array of Criteria which is a user-friendly way of querying the database.</param>
        /// <param name="OrderBy">The detail of the image to order the returned values by.</param>
        /// <param name="LimitTo">The number of images to limit the return value to.</param>
        /// <returns>An ImageList of images that match the specified criteria, ordered by the detail specified. It is limited to the number of images specified, although the number of images may be less.</returns>
        public ImageList Select(ImageCriteria[] Crit, ImageDetail OrderBy, int LimitTo)
        {
            // Call private function with two query extensions
            return Select(Crit, " LIMIT TO " + LimitTo.ToString() + " ORDER BY " + ImageDetailToField(OrderBy));
        }
        
        /// <summary>
        /// Private function to select images from the database. Accepts ExtraSQL to allow for the many public overloads. 
        /// </summary>
        /// <param name="Crit">An array of Criteria which is a user-friendly way of querying the database.</param>
        /// <param name="ExtraSQL">A string of Extra SQL to add to the statement. Must start with a space.</param>
        /// <returns>An ImageList of images that match the specified criteria. May be affected by the Extra SQL.</returns>
        private ImageList Select(ImageCriteria[] Crit, string ExtraSQL)
        {

            // Get WHERE Statement
            string WhereSQL = CriteriaToQuery(Crit);

            // Construct final query
            string SQL = "SELECT * FROM " + Table + WhereSQL + ExtraSQL;

            // Run Query & return result
            return Query(SQL);

        }

        #endregion

        #region Other Functions

        /// <summary>
        /// Open a database.
        /// </summary>
        /// <param name="DatabaseFile">Filename of the database to open, including the extension.</param>
        public void OpenDatabase(string DatabaseFile)
        {

            // Open database
            DB = (IDbConnection)new SqliteConnection("URI=file:" + DatabaseFile);
            DB.Open();

            // Create a command; if we make it global saves assigning memory repeatedly.. etc.
            Cmd = DB.CreateCommand();

        }

        /// <summary>
        /// Add an image to the database.
        /// </summary>
        /// <param name="Details"></param>
        public void Add(ImageValue[] Details)
        {

            // Query will take the form "INSERT INTO <table> (<fields>) VALUES (<values>)".

            // Sort (<fields>) and (<values>) parts
                string Fields = " (";
                string Values = " (";
                // Add each field & value
                foreach (ImageValue Image in Details)
                {
                    Fields += Image.Detail.ToString() + ", ";
                    Values += Image.Value + ", ";
                }
                // Remove trailling , and add a )
                Fields = Fields.Substring(0, Fields.Length - 2) + ")";
                Values = Values.Substring(0, Values.Length - 2) + ")";

            // Constrol query
            string SQL = "INSERT INTO " + Table + Fields + " VALUES" + Values;

            // Run query
            Query(SQL);

            // Todo: Error checking lawl.

        }
        
        public void Update(ImageValue[] Details, ImageCriteria[] Criteria)
        {

            // Query will take the form "UPDATE <table> SET <field>=<value> WHERE <criteria>"

            // Handle SET stuff
                string Set = " SET ";
                // Foreach detail we are changing
                foreach (ImageValue Detail in Details)
                    Set += Detail.Detail.ToString() + "=" + Detail.Value + " ";
                // Remove the last space; the WHERE block has a starting space >.>
                Set = Set.Substring(0, Set.Length - 1);

            // Get WHERE statement
            string Where = CriteriaToQuery(Criteria);

            // Construct query
            string SQL = "UPDATE " + Table + Set + Where;

            // Run!
            Query(SQL);

            // Todo: Error check?

        }

        public void Delete(ImageCriteria[] Criteria)
        {

            // Query will be in the form "DELETE FROM <table> WHERE <criteria>"

            // Get WHERE block
            string Where = CriteriaToQuery(Criteria);

            // Construct Query
            string SQL = "DELETE FROM " + Table + Where;

            // Run!
            Query(SQL);

            // Todo: Error check?

        }

        #endregion

        #region Private Functions

        // Query
        private ImageList Query(string SQL)
        {

            // Make
            Cmd.CommandText = SQL;

            // Do and get results!
            IDataReader results = Cmd.ExecuteReader();

            // Parse & return results
            return ResultsToImageList(results);

        }

        // Results to Dictionary
        private ImageList ResultsToImageList(IDataReader results)
        {

            // Vars
            ImageList List = new ImageList(results.RecordsAffected);
            string fieldName;
            int j = 0;

            // For each result 
            while (results.Read())
            {

                // For each field
                for (int i = 0; i < results.FieldCount; i++)
                {

                    // What's the field name?
                    fieldName = results.GetName(i);

                    // Add to dictionary
                    List.Add(j, FieldToImageDetail(fieldName), results.GetString(i));

                }

                j++;

            }

            return List;

        }

        // Criteria -> WHERE statement
        private string CriteriaToQuery(ImageCriteria[] Crit)
        {

            // No criteria?
            if (Crit.Length == 0)
                return String.Empty;

            // Var
            string Query = " WHERE"; // All parts of query should start with a blank space so they can be appended to previous parts.
            string field, relationship;

            // For each criteria.. (haha, fooled you, it isn't a foreach loop.)
            for (int i = 0; i < Crit.Length; i++)
            {

                // Need AND?
                if (i != 0)
                    Query += " AND";

                // Get field
                field = ImageDetailToField(Crit[i].Detail);

                // Get Relationship
                switch (Crit[i].Relationship)
                {
                    case Relationship.Contains:
                        relationship = " LIKE ";
                        break;
                    case Relationship.EqualTo:
                        relationship = "=";
                        break;
                    case Relationship.GreaterThan:
                        relationship = ">";
                        break;
                    case Relationship.GreaterThanOrEqualTo:
                        relationship = ">=";
                        break;
                    case Relationship.LessThan:
                        relationship = "<";
                        break;
                    case Relationship.LessThanOrEqualTo:
                        relationship = "<=";
                        break;
                    case Relationship.Not:
                        relationship = "!=";
                        break;
                    default:
                        throw new Exception("Unknown enum value; DBWrapper needs updating!");
                }

                // Construct query
                Query += " " + field + relationship + "'" + Crit[i].Value + "'";

            }

            return Query;

        }

        // ImageDetail -> Real Field Name
        private string ImageDetailToField(ImageDetail Detail)
        {

            // Find out what field - not the pseudo name given by ImageDetail enum
            string field;
            switch (Detail)
            {
                case ImageDetail.Checksum:
                    return "checksum";
                case ImageDetail.Filename:
                    return "filename";
                case ImageDetail.Filesize:
                    // We can't order by filesize ourselves .. it isn't in the database.
                    // We'll sort something out so that we will use the FSWrapper to do it .. eventually... TODO :)
                    // For now, just get it to order by ID.
                    throw new NotImplementedException("DB Wrapper can't order by ID; we must later link it to FSWrapper to do so!");
                case ImageDetail.ID:
                    return "id";
                case ImageDetail.Path:
                    return "path";
                case ImageDetail.Tags:
                    return "tags";
                default:
                    throw new Exception("Unknown enum value; DBWrapper needs updating!");
            }

        }

        // Real Field Name -> ImageDetail
        private ImageDetail FieldToImageDetail(string Field)
        {

            // Bah.
            switch (Field)
            {
                case "checksum":
                    return ImageDetail.Checksum;
                case "filename":
                    return ImageDetail.Filename;
                // Can't be filesize; field doesn't exist.
                case "id":
                    return ImageDetail.ID;
                case "path":
                    return ImageDetail.Path;
                case "tags":
                    return ImageDetail.Tags;
                default:
                    throw new Exception("Unknown field name; DBWrapper needs updating!");
            }

        }

        #endregion

    }
}
